package com.live.shop.sql;

import android.annotation.SuppressLint;
import android.content.ContentValues;
import android.content.Context;
import android.database.Cursor;
import android.database.sqlite.SQLiteDatabase;

import com.live.shop.model.KeywordModel;
import com.live.shop.model.KeywordRecordModel;

import java.io.File;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class KeywordListDatabase {
	private static MyDatabaseHelper helper;
	public KeywordListDatabase(Context context){
		//创建数据库和表
		helper = new MyDatabaseHelper(context, "user.db",null,3,null);
	}

	//查询所有
	@SuppressLint("Range")
	public static List<KeywordModel> selectAll() {
		List<KeywordModel> list = new ArrayList<>();
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor1 = database.rawQuery("select * from keyword_list ", null);
		if (cursor1 != null && cursor1.getCount() > 0) {
			while (cursor1.moveToNext()) {
				KeywordModel model = new KeywordModel();
				int keyword_id = cursor1.getInt(cursor1.getColumnIndex("keyword_id"));
				model.setKeywordId(keyword_id);
				model.setQuestion(cursor1.getString(cursor1.getColumnIndex("question")));
				model.setKeyword(cursor1.getString(cursor1.getColumnIndex("keyword")));
				model.setCreateTime(cursor1.getString(cursor1.getColumnIndex("create_time")));
				model.setUseNum(cursor1.getInt(cursor1.getColumnIndex("use_num")));
				model.setKeywordStatus(cursor1.getInt(cursor1.getColumnIndex("keyword_status")));

				List<KeywordRecordModel> recordList = new ArrayList<>();
				Cursor cursor2 = database.rawQuery("select * from keyword_record where  keyword_id = "+keyword_id, null);
				if (cursor2 != null && cursor2.getCount() > 0) {
					while (cursor2.moveToNext()) {
						KeywordRecordModel re = new KeywordRecordModel();
						re.setId(cursor2.getInt(cursor2.getColumnIndex("id")));
						re.setKeywordId(cursor2.getInt(cursor2.getColumnIndex("keyword_id")));
						re.setAnswerTxt(cursor2.getString(cursor2.getColumnIndex("answer_txt")));
						re.setAnswerAudio(cursor2.getString(cursor2.getColumnIndex("answer_audio")));
						re.setAnswerState(cursor2.getInt(cursor2.getColumnIndex("answer_state")));
						re.setAnswerType(cursor2.getInt(cursor2.getColumnIndex("answer_type")));
						re.setUseNum(cursor2.getInt(cursor2.getColumnIndex("use_num")));
						re.setCreateTime(cursor2.getString(cursor2.getColumnIndex("create_time")));
						re.setDeleteStatus(2);//删除状态 1新增 2编辑 3删除
						recordList.add(re);
					}
					cursor2.close();
				}
				model.setListRecord(recordList);

				list.add(model);
			}
			cursor1.close();
		}
		database.close();
		return list;
	}

	//查询启用中的
	@SuppressLint("Range")
	public static List<KeywordModel> selectAllStatusYes() {
		List<KeywordModel> list = new ArrayList<>();
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor1 = database.rawQuery("select * from keyword_list where keyword_status=1 ", null);
		if (cursor1 != null && cursor1.getCount() > 0) {
			while (cursor1.moveToNext()) {
				KeywordModel model = new KeywordModel();
				int keyword_id = cursor1.getInt(cursor1.getColumnIndex("keyword_id"));
				model.setKeywordId(keyword_id);
				model.setQuestion(cursor1.getString(cursor1.getColumnIndex("question")));
				model.setKeyword(cursor1.getString(cursor1.getColumnIndex("keyword")));
				model.setCreateTime(cursor1.getString(cursor1.getColumnIndex("create_time")));
				model.setUseNum(cursor1.getInt(cursor1.getColumnIndex("use_num")));
				model.setKeywordStatus(cursor1.getInt(cursor1.getColumnIndex("keyword_status")));

				List<KeywordRecordModel> recordList = new ArrayList<>();
				Cursor cursor2 = database.rawQuery("select * from keyword_record where  keyword_id = "+keyword_id, null);
				if (cursor2 != null && cursor2.getCount() > 0) {
					while (cursor2.moveToNext()) {
						KeywordRecordModel re = new KeywordRecordModel();
						re.setId(cursor2.getInt(cursor2.getColumnIndex("id")));
						re.setKeywordId(cursor2.getInt(cursor2.getColumnIndex("keyword_id")));
						re.setAnswerTxt(cursor2.getString(cursor2.getColumnIndex("answer_txt")));
						re.setAnswerAudio(cursor2.getString(cursor2.getColumnIndex("answer_audio")));
						re.setAnswerState(cursor2.getInt(cursor2.getColumnIndex("answer_state")));
						re.setAnswerType(cursor2.getInt(cursor2.getColumnIndex("answer_type")));
						re.setUseNum(0);
						re.setCreateTime(cursor2.getString(cursor2.getColumnIndex("create_time")));
						re.setDeleteStatus(2);//删除状态 1新增 2编辑 3删除
						recordList.add(re);
					}
					cursor2.close();
				}
				model.setListRecord(recordList);

				list.add(model);
			}
			cursor1.close();
		}
		database.close();
		return list;
	}

	//查询本地当前数量
	@SuppressLint("Range")
	public static String[] selectNum() {
		String[] result = new String[2];
		SQLiteDatabase database = helper.getReadableDatabase();
		Cursor cursor = database.rawQuery("SELECT COUNT(keyword_id) AS keyword_num,SUM(use_num) AS use_num FROM keyword_list", null);
		if (cursor != null && cursor.getCount() > 0) {
			if (cursor.moveToNext()) {
				result[0] = String.valueOf(cursor.getInt(cursor.getColumnIndex("keyword_num")));
				result[1] = String.valueOf(cursor.getInt(cursor.getColumnIndex("use_num")));
			}
			cursor.close();
		}
		database.close();
		return result;
	}
	//新增
	public static void insertData(KeywordModel keyModel) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("question", keyModel.getQuestion());
		values.put("keyword", keyModel.getKeyword());
		values.put("keyword_status", 1);//启用状态 0未启用 1启用
		values.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
		long keyword_id = database.insert("keyword_list", null, values);
		values.clear();

		List<KeywordRecordModel> recordList = keyModel.getListRecord();
		for (KeywordRecordModel rm : recordList) {
			ContentValues values2 = new ContentValues();
			values2.put("keyword_id", keyword_id);
			values2.put("answer_txt", rm.getAnswerTxt());
			values2.put("answer_type", rm.getAnswerType());
			values2.put("answer_state", rm.getAnswerState());
			values2.put("answer_audio", rm.getAnswerAudio());
			values2.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
			database.insert("keyword_record", null, values2);
			values2.clear();
		}
		database.close();
	}
	//更改启用状态
	@SuppressLint("Range")
	public static void updateKeywordStatus(int keyword_id, int keyword_status) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values = new ContentValues();
		values.put("keyword_status", keyword_status);
		database.update("keyword_list", values, "keyword_id = ?", new String[] {String.valueOf(keyword_id)});
		values.clear();
		database.close();
	}
	//更改数据
	@SuppressLint("Range")
	public static void updateData(KeywordModel keyModel) {
		SQLiteDatabase database = helper.getReadableDatabase();
		ContentValues values1 = new ContentValues();
		values1.put("question", keyModel.getQuestion());
		values1.put("keyword", keyModel.getKeyword());
		database.update("keyword_list", values1, "keyword_id = ?", new String[] {String.valueOf(keyModel.getKeywordId())});
		values1.clear();

		List<KeywordRecordModel> recordList = keyModel.getListRecord();
		for (KeywordRecordModel rm : recordList) {
			//删除状态 1新增 2编辑 3删除
			ContentValues values2 = new ContentValues();
			values2.put("keyword_id", keyModel.getKeywordId());
			values2.put("answer_txt", rm.getAnswerTxt());
			values2.put("answer_type", rm.getAnswerType());
			values2.put("answer_state", rm.getAnswerState());
			values2.put("answer_audio", rm.getAnswerAudio());
			switch (rm.getDeleteStatus()){
				case 1://新增
					values2.put("create_time", new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()));
					database.insert("keyword_record", null, values2);
					break;
				case 2://编辑
					//旧音频和新音频不一样则删除
					deleteDifferentFile(database,rm.getId(),rm.getAnswerAudio());
					//更新该记录
					database.update("keyword_record", values2, "id = ?", new String[] {String.valueOf(rm.getId())});
					break;
				case 3://删除
					//删除文件
					deleteFileOne(database,rm.getId());
					//删除该记录
					database.execSQL("delete FROM keyword_record WHERE id = "+rm.getId());
					break;
			}
			values2.clear();
		}
		database.close();
	}
	//仅更改使用次数
	@SuppressLint("Range")
	public static void updateUseNumData(List<KeywordModel> list) {
		SQLiteDatabase database = helper.getReadableDatabase();
		for (KeywordModel keyModel:list) {
			database.execSQL("UPDATE keyword_list SET use_num = "+keyModel.getUseNum()+" WHERE keyword_id = "+keyModel.getKeywordId());
			//更改
			List<KeywordRecordModel> recordList = keyModel.getListRecord();
			for (KeywordRecordModel rm : recordList) {
				database.execSQL("UPDATE keyword_record SET use_num = use_num + "+rm.getUseNum()+" WHERE id = "+rm.getId());
			}
		}
		database.close();
	}
	//删除关键词某条记录
	public static void deleteRecordData(int id) {
		SQLiteDatabase database = helper.getReadableDatabase();
		database.execSQL("delete FROM keyword_record WHERE id = "+id);
		deleteFileOne(database,id);
		database.close();
	}

	//删除关键词
	public static void deleteData(int keyword_id) {
		SQLiteDatabase database = helper.getReadableDatabase();
		database.execSQL("delete FROM keyword_list WHERE keyword_id = "+keyword_id);
		deleteFileALL(database,keyword_id);
		database.execSQL("delete FROM keyword_record WHERE keyword_id = "+keyword_id);
		database.close();
	}
	//先获取音频路径并且删除文件
	@SuppressLint("Range")
	public static void deleteFileOne(SQLiteDatabase database,int id){
		Cursor cursorDel = database.rawQuery("select * from keyword_record where id="+id, null);
		if (cursorDel != null && cursorDel.getCount() > 0) {
			if (cursorDel.moveToNext()) {
				String answer_audio_old = cursorDel.getString(cursorDel.getColumnIndex("answer_audio"));
				int answerType = cursorDel.getInt(cursorDel.getColumnIndex("answer_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 2){
					//文件存在则删除
					File file = new File(answer_audio_old);
					if(file.exists()){
						file.delete();
					}
				}
			}
			cursorDel.close();
		}
	}
	//先获取音频路径并且删除文件
	@SuppressLint("Range")
	public static void deleteFileALL(SQLiteDatabase database,int keyword_id){
		Cursor cursorDel = database.rawQuery("select * from keyword_record where keyword_id="+keyword_id, null);
		if (cursorDel != null && cursorDel.getCount() > 0) {
			while (cursorDel.moveToNext()) {
				String answer_audio_old = cursorDel.getString(cursorDel.getColumnIndex("answer_audio"));
				int answerType = cursorDel.getInt(cursorDel.getColumnIndex("answer_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 2){
					//文件存在则删除
					File file = new File(answer_audio_old);
					if(file.exists()){
						file.delete();
					}
				}
			}
			cursorDel.close();
		}
	}
	//旧音频和新音频不一样则删除
	@SuppressLint("Range")
	public static void deleteDifferentFile(SQLiteDatabase database,int id,String new_answer_audio){
		Cursor cursorEdit = database.rawQuery("select * from keyword_record where id="+id, null);
		if (cursorEdit != null && cursorEdit.getCount() > 0) {
			if (cursorEdit.moveToNext()) {
				String answer_audio_old = cursorEdit.getString(cursorEdit.getColumnIndex("answer_audio"));
				int answerType = cursorEdit.getInt(cursorEdit.getColumnIndex("answer_type"));
				//合成方式 0.暂无 1.录音 2.AI合成 3.本地音频
				if(answerType == 1 || answerType == 2){
					//旧音频和新音频不一样
					if(!new_answer_audio.equals(answer_audio_old)){
						//文件存在则删除
						File file = new File(answer_audio_old);
						if(file.exists()){
							file.delete();
						}
					}
				}
			}
			cursorEdit.close();
		}
	}
}
